import arcpy
import sys
import os
import shutil
import tempfile
from stormwaterutilities import CopyFieldFromFeatureClass
from stormwaterutilities import IdentifyFeaturesFromTable
from stormwaterutilities import addMessage
from polylinerasterintersect import PolylineRasterIntersect

def AssignSusceptibilityScores(links, nodes):
    addMessage("*** Assigning susceptibility scores ***")
    addMessage("*** Assigning node scores ***")

    joinField = 'OBJECTID'
    scoreField = 'wq_susc_score'

    if len(arcpy.ListFields(nodes,scoreField)) == 0:
        arcpy.AddField_management(nodes, scoreField, "FLOAT")

    if len(arcpy.ListFields(links,scoreField)) == 0:
        arcpy.AddField_management(links, scoreField, "FLOAT")


    #Surface Management Facility = 2
    fromWhereClause = """ "wq_susc_class"= 'Surface Management Facility' """
    IdentifyFeaturesFromTable(nodes, joinField, scoreField, 2, nodes, joinField, fromWhereClause=fromWhereClause)

    #UIC = 1
    fromWhereClause = """ "wq_susc_class"= 'UIC' """
    IdentifyFeaturesFromTable(nodes, joinField, scoreField, 1, nodes, joinField, fromWhereClause=fromWhereClause)

    #UIC near water well = 3
    fromWhereClause = """ "wq_susc_class"= 'UIC near water well' """
    IdentifyFeaturesFromTable(nodes, joinField, scoreField, 3, nodes, joinField, fromWhereClause=fromWhereClause)

    #Outfall = 4
    fromWhereClause = """ SUBSTRING("wq_susc_class", 1, 2) = 'of' """
    IdentifyFeaturesFromTable(nodes, joinField, scoreField, 4, nodes, joinField, fromWhereClause=fromWhereClause)

    addMessage("*** Assigning link scores ***")

    #Constructed or ROW Surface Drainage = 1
    fromWhereClause = """ "wq_susc_class"= 'Constructed or ROW Surface Drainage' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 2, links, joinField, fromWhereClause=fromWhereClause)

    #Natural Drainage wth Fish = 5
    fromWhereClause = """ "wq_susc_class"= 'Natural Drainages with Fish' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 5, links, joinField, fromWhereClause=fromWhereClause)

    #Natural Drainages without Fish = 4
    fromWhereClause = """ "wq_susc_class"= 'Natural Drainages without Fish' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 4, links, joinField, fromWhereClause=fromWhereClause)

    #Small surface flow path
    fromWhereClause = """ "wq_susc_class"= 'Incidental Surface Flow Path' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 2, links, joinField, fromWhereClause=fromWhereClause)


    #Natural Wetland = 4
    fromWhereClause = """ "wq_susc_class"= 'Natural Wetland' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 4, links, joinField, fromWhereClause=fromWhereClause)

    #Piped Drainage = 1
    fromWhereClause = """ "wq_susc_class"= 'Piped Drainage' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 1, links, joinField, fromWhereClause=fromWhereClause)

    #Private Impervious Flowpath = 2
    fromWhereClause = """ "wq_susc_class"= 'Private Impervious Flowpath' """
    IdentifyFeaturesFromTable(links, joinField, scoreField, 1, links, joinField, fromWhereClause=fromWhereClause)

def AssignSusceptibility(links, nodes):
    # Set environment
    arcpy.env.overwriteOutput=True

    # Connect to CGIS hub
    script_dir = os.path.dirname(os.path.abspath(__file__))
    sde_path = os.path.join(script_dir, "GISDB1.ROSE.PORTLAND.LOCAL.sde")

    # Add Hansen Unit ID to target
    addMessage("*** Adding Hansen Unit IDs from nodes***")
    targetField = "UNITID"

    fromFc = r"\\ws18325\Stormwater D\RiskModel\Drainage\PipeNetwork.gdb\NetworkDataset\dme_nodes"
    fromField = "UNITID"

    CopyFieldFromFeatureClass(nodes, 'GLOBALID', targetField, fromFc, 'GLOBALID', fromField)

    addMessage("*** Adding Hansen Unit IDs from inlets***")
    fromFc = r"\\ws18325\Stormwater D\RiskModel\Drainage\PipeNetwork.gdb\NetworkDataset\dme_inlets"
    CopyFieldFromFeatureClass(nodes, 'GLOBALID', targetField, fromFc, 'GLOBALID', fromField)


    #ID UICs
    addMessage("*** Identifying UICs ***")
    targetJoinField = "UNITID"
    targetField = "wq_susc_class"

    whereClause= r""" "SYMBOL_GROUP" = 'SUMPS' """
    fromFc = r"\\ws18325\Stormwater C\SWSP\Scripts\GISDB1.ROSE.PORTLAND.LOCAL.sde\EGH_PUBLIC.ARCMAP_ADMIN.collection_points_bes_pdx"
    fromJoinField = "UNITID"
    calcValue="UIC"

    IdentifyFeaturesFromTable(nodes, targetJoinField, targetField, calcValue, fromFc, fromJoinField, fromWhereClause=whereClause)

    #ID UICs near drinking water wells
    addMessage("*** Identifying UICs near drinking water wells ***")
    targetJoinField = "UNITID"
    targetField = "wq_susc_class"
    calcValue = "UIC near water well"

    fromFc = r"\\ws18325\Stormwater C\SWSP\Scripts\GISDB1.ROSE.PORTLAND.LOCAL.sde\EGH_PUBLIC.ARCMAP_ADMIN.uic_bes_pdx"
    fromJoinField = "UNITID"
    whereClause= """ "Ops_Status" = 'AC' AND CAST("Distance_to_water_well" as INTEGER) <= 500 """

    IdentifyFeaturesFromTable(nodes, targetJoinField, targetField, calcValue, fromFc, fromJoinField, fromWhereClause=whereClause)


    #Populate new field with Basin values from the outfalls fc
    addMessage("***Populating basin values***")
    targetJoinField = "UNITID"
    targetField = "wq_susc_class"
    fromFc = r"\\ws18325\Stormwater C\SWSP\Scripts\GISDB1.ROSE.PORTLAND.LOCAL.sde\EGH_PUBLIC.ARCMAP_ADMIN.of_points_bes_pdx"
    fromJoinField = "HANSEN_ID"
    fromField = "Basin"
    fromWhereClause= """ "HANSEN_ID" != 'XXXX' """

    CopyFieldFromFeatureClass(nodes, targetJoinField, targetField, fromFc, fromJoinField, fromField, prependString = 'of_', whereClause=fromWhereClause )

    #ID Surface Management Facilities (BMP/ PRFs)
    addMessage("***Identifying BMPs***")
    targetJoinField = "UNITID"
    targetField = "wq_susc_class"
    calcValue = "Surface Management Facility"

    fromFc = r"\\ws18325\Stormwater D\RiskModel\RiskAssessment\WaterQuality\SusceptibilitySourceData.gdb\surface_management_facilities"
        #Note above was originally at r"\\cassio\ASM_Projects\9ESPP0000055_NPDES\2011\01Benchmarks\T03_StructuralBMPs\City_wide_MS4\Working\Effort_2\PGDB\BMP_Update_Oct2012.gdb\BMP_merge"
    fromJoinField = "Nearest_Hansen"

    fromWhereClause = """ "In_Stream"=0 AND "Lumped"=0 AND "Dupe_wDME"<>1 AND "Remove"=0 AND "Orig_Source"<>'DMEpy' AND "Private"=0 AND "BMP_TYPE_clean" IN ('Ditch','Dry Pond','Ecoroof','Planter','Pollution Reduction Facility','Roadside Treatment Facility','Sidewalk Planter','Storm Infiltration Trench') AND "Nearest_Hansen"<>'' AND "Nearest_Hansen" IS NOT NULL  """
    IdentifyFeaturesFromTable(nodes, targetJoinField, targetField, calcValue, fromFc, fromJoinField, fromWhereClause=fromWhereClause)


    #intersect polylines with raster to append raster values
    addMessage("*** Populating polylines from raster ***")
    PolylineRasterIntersect(links,
        r"\\ws18325\Stormwater D\RiskModel\RiskAssessment\WaterQuality\SusceptibilitySourceData.gdb\surface_susc_class", "susc_class", "swsp")
        #Note above was oringally at r"\\oberon\StormWaterProgram\Data\GIS\StormwaterToolbox\Risk\WQ\Working\SWSP_working.gdb\wetland_ImpAROW_bit"

    CopyFieldFromFeatureClass(links, 'OBJECTID', 'wq_susc_class', links, 'OBJECTID', 'swsp_dominant', """ "swsp_Dominant"<>'' AND "swsp_Dominant" IS NOT NULL """)

    #identify piped systems
    addMessage("*** Identifying piped systems ***")
    IdentifyFeaturesFromTable(links, 'OBJECTID', 'wq_susc_class', 'Piped Drainage', links,'OBJECTID', targetWhereClause=""" "SourceClass"='dme_links' """)


    #Populate field with Drainage values
    addMessage("*** identifying Natural Drainages with Fish ***")

    targetJoinField = "GlobalID"
    targetField = "wq_susc_class"
    calcValue = 'Natural Drainages with Fish'

    fromFc = r"\\ws18325\Stormwater D\RiskModel\RiskAssessment\WaterQuality\SusceptibilitySourceData.gdb\fish_drainages"
        #Note above was originally at r"\\oberon\StormWaterProgram\Data\GIS\StormwaterToolbox\Risk\WQ\Working\SWSP_working.gdb\fish_drainages"
    fromJoinField = "GlobalID"

    IdentifyFeaturesFromTable(links, targetJoinField, targetField, calcValue, fromFc, fromJoinField, targetWhereClause=""" "SourceClass" = 'dme_surface_water' """)
    #runs through but identifies incorrect record set - bad UID


    #Populate field with Drainage values - always run this LAST as it populates all records not yet filled
    addMessage("*** identifying Natural Drainages without Fish ***")
    targetField = "wq_susc_class"
    calcValue = 'Natural Drainages without Fish'
    IdentifyFeaturesFromTable(links, 'OBJECTID', targetField, calcValue, links, 'OBJECTID', fromWhereClause=""" "wq_susc_class"='' OR "wq_susc_class" IS NULL  AND "accumulated_area_sq_ft" >= 43560 """)

    addMessage("*** identifying incidental surface flow path ***")
    targetField = "wq_susc_class"
    calcValue = 'Incidental Surface Flow Path'
    IdentifyFeaturesFromTable(links, 'OBJECTID', targetField, calcValue, links, 'OBJECTID', fromWhereClause=""" "wq_susc_class"='' OR "wq_susc_class" IS NULL  AND "accumulated_area_sq_ft" < 43560 """)


    AssignSusceptibilityScores(links, nodes)

if __name__ == '__main__':
    AssignSusceptibility(r"\\ws18325\Stormwater D\RiskModel\RiskAssessment\WaterQuality\WQRiskNetwork.gdb\WQConveyance_net",
        r"\\ws18325\Stormwater D\RiskModel\RiskAssessment\WaterQuality\WQRiskNetwork.gdb\WQNodes_net")
